import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import xlwings as xw
import os

class ExcelProcessor:
    def __init__(self):
        self.window = tk.Tk()
        self.window.title("快报导入综合计划建议附表")
        self.window.geometry("600x450")
        
        self.source_file = tk.StringVar()
        self.target_file = tk.StringVar()
        
        self.create_widgets()
        
    def create_widgets(self):
        # 文件选择框架
        file_frame = ttk.LabelFrame(self.window, text="文件选择", padding=10)
        file_frame.pack(fill="x", padx=10, pady=5)
        
        # 源文件选择
        ttk.Label(file_frame, text="快报文件:").pack(anchor="w")
        ttk.Entry(file_frame, textvariable=self.source_file, width=50).pack(side="left", padx=5)
        ttk.Button(file_frame, text="选择文件", command=self.select_source_file).pack(side="left")
        
        # 目标文件选择
        target_frame = ttk.LabelFrame(self.window, text="目标文件", padding=10)
        target_frame.pack(fill="x", padx=10, pady=5)
        ttk.Label(target_frame, text="综合计划建议附表:").pack(anchor="w")
        ttk.Entry(target_frame, textvariable=self.target_file, width=50).pack(side="left", padx=5)
        ttk.Button(target_frame, text="选择文件", command=self.select_target_file).pack(side="left")
        
        # 进度条
        self.progress = ttk.Progressbar(self.window, length=400, mode='determinate')
        self.progress.pack(pady=10)
        
        # 处理按钮
        ttk.Button(self.window, text="开始处理", command=self.process_files).pack(pady=10)
        
        # 状态标签
        self.status_label = ttk.Label(self.window, text="")
        self.status_label.pack(pady=5)

    def select_source_file(self):
        filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
        if filename:
            self.source_file.set(filename)
            self.status_label.config(text="已选择源文件")

    def select_target_file(self):
        filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
        if filename:
            self.target_file.set(filename)
            self.status_label.config(text="已选择目标文件")

    def read_excel_data(self, worksheet, col, cell_range):
        """读取Excel数据的辅助函数"""
        try:
            if '+' in cell_range:
                ranges = cell_range.split('+')
                return sum(worksheet.range(f'{col}{r.strip()}').value or 0 for r in ranges)
            return worksheet.range(f'{col}{cell_range}').value
        except Exception as e:
            raise Exception(f"读取单元格 {col}{cell_range} 时出错: {str(e)}")

    def get_data_from_source(self, worksheet, col):
        """从源文件读取数据"""
        data = {}
        
        # 基础数据读取
        data['fdl'] = self.read_excel_data(worksheet, col, '442')  # 发电量
        data['fcsdl'] = self.read_excel_data(worksheet, col, '463')  # 非生产用电量
        data['sdl'] = self.read_excel_data(worksheet, col, '469')  # 售电量
        data['fdcydl'] = self.read_excel_data(worksheet, col, '482') / 100  # 发电厂用电率
        data['grcydl'] = self.read_excel_data(worksheet, col, '492')  # 供热厂用电率
        data['grl'] = self.read_excel_data(worksheet, col, '477') / 10000  # 供热量
        data['srl'] = self.read_excel_data(worksheet, col, '479') / 10000  # 售热量
        data['gdmh'] = self.read_excel_data(worksheet, col, '548')  # 供电煤耗
        data['grmh'] = self.read_excel_data(worksheet, col, '555')  # 供热煤耗
        data['fdbmj'] = self.read_excel_data(worksheet, col, '549')  # 发电标煤单价
        data['grbmj'] = self.read_excel_data(worksheet, col, '556')  # 供热标煤单价
        data['sddj'] = self.read_excel_data(worksheet, col, '513')  # 售电单价
        data['srdj'] = self.read_excel_data(worksheet, col, '538')  # 售热单价
        data['qtywsr'] = self.read_excel_data(worksheet, col, '117')  # 其它业务收入
        data['sf'] = self.read_excel_data(worksheet, col, '332+379')  # 水费
        data['wgdf'] = self.read_excel_data(worksheet, col, '333')  # 网购电费
        data['clf'] = self.read_excel_data(worksheet, col, '335+380')  # 材料费
        data['zgxc'] = self.read_excel_data(worksheet, col, '338+381')  # 职工薪酬
        data['gz'] = self.read_excel_data(worksheet, col, '339+382')  # 工资
        data['zjf'] = self.read_excel_data(worksheet, col, '341+384')  # 折旧费
        data['xlf'] = self.read_excel_data(worksheet, col, '342+385')  # 修理费
        data['wtyx'] = self.read_excel_data(worksheet, col, '343+386')  # 委托运行费
        data['qtfy'] = self.read_excel_data(worksheet, col, '344+387')  # 其他费用
        data['qtywcb'] = self.read_excel_data(worksheet, col, '140')  # 其他业务成本
        data['yysj'] = self.read_excel_data(worksheet, col, '151')  # 营业税金
        data['cwfy'] = self.read_excel_data(worksheet, col, '195')  # 财务费用
        data['qtsy'] = self.read_excel_data(worksheet, col, '210')  # 其他收益
        data['yywsr'] = self.read_excel_data(worksheet, col, '287')  # 营业外收入
        data['yywzc'] = self.read_excel_data(worksheet, col, '299')  # 营业外支出
        data['yffy'] = self.read_excel_data(worksheet, col, '184')  # 研发费用
        data['glfy'] = self.read_excel_data(worksheet, col, '173')  # 管理费用

        # 计算派生数据
        data['fdzhbml'] = (data['gdmh'] * (data['fdl'] - data['fdl'] * data['fdcydl'])) / 1000000
        data['fdywrlcb'] = data['fdbmj'] * data['fdzhbml']
        data['grywrlcb'] = data['grbmj'] * data['grmh'] * data['grl'] / 1000
        data['dlywsr'] = data['sddj'] * data['sdl'] / 1000
        data['grywsr'] = data['srl'] * data['srdj']
        data['zyywsr'] = data['dlywsr'] + data['grywsr']
        data['crlwcb'] = data['sf'] + data['wgdf'] + data['clf'] + data['zgxc'] + data['zjf'] + data['xlf'] + data['wtyx'] + data['qtfy']
        data['rlf'] = data['fdywrlcb'] + data['grywrlcb']
        data['zyywcb'] = data['rlf'] + data['crlwcb']
        data['yycb'] = data['zyywcb'] + data['qtywcb']
        
        return data

    def write_data_to_target(self, worksheet, col, data):
        """写入数据到目标工作表"""
        # 基础数据写入
        worksheet.range(f'{col}3').value = data['fdl']  # 发电量
        worksheet.range(f'{col}4').value = data['fdl'] * data['fdcydl']  # 发电厂用电量
        worksheet.range(f'{col}5').value = data['fdl'] - data['fdl'] * data['fdcydl']  # 供电量
        worksheet.range(f'{col}6').value = data['grcydl'] * data['grl']  # 供热厂用电量
        worksheet.range(f'{col}7').value = data['fdl'] - data['fdl'] * data['fdcydl'] - data['grcydl'] * data['grl']  # 厂供电量
        worksheet.range(f'{col}9').value = data['fcsdl']  # 非生产用电量
        worksheet.range(f'{col}10').value = data['sdl']  # 售电量
        worksheet.range(f'{col}11').value = data['fdcydl']  # 发电厂用电率
        worksheet.range(f'{col}12').value = data['grcydl']  # 供热厂用电率
        worksheet.range(f'{col}15').value = data['grl']  # 供热量
        worksheet.range(f'{col}16').value = data['srl']  # 售热量
        worksheet.range(f'{col}17').value = data['gdmh']  # 供电煤耗
        worksheet.range(f'{col}18').value = data['fdzhbml']  # 发电综合标煤量
        worksheet.range(f'{col}19').value = data['grmh']  # 供热煤耗
        worksheet.range(f'{col}20').value = data['grmh'] * data['grl'] / 1000  # 供热综合标煤量
        
        # 写入计算结果
        worksheet.range(f'{col}22').value = data['fdbmj']  # 发电标煤单价
        worksheet.range(f'{col}23').value = data['grbmj']  # 供热标煤单价
        worksheet.range(f'{col}24').value = data['rlf'] / (data['fdzhbml'] + data['grmh'] * data['grl'] / 1000)  # 发电供热综合标煤单价
        worksheet.range(f'{col}25').value = data['sddj']  # 售电单价
        worksheet.range(f'{col}26').value = data['srdj']  # 售热单价
        worksheet.range(f'{col}27').value = data['zyywsr'] + data['qtywsr']  # 营业收入
        worksheet.range(f'{col}28').value = data['zyywsr']  # 主营业务收入
        worksheet.range(f'{col}29').value = data['dlywsr']  # 电力业务收入
        worksheet.range(f'{col}30').value = data['grywsr']  # 供热业务收入
        worksheet.range(f'{col}31').value = data['qtywsr']  # 其它业务收入
        
        # 写入成本相关数据
        worksheet.range(f'{col}32').value = data['yycb']  # 营业成本
        worksheet.range(f'{col}33').value = data['zyywcb']  # 主营业务成本
        worksheet.range(f'{col}34').value = data['rlf']  # 燃料费
        worksheet.range(f'{col}35').value = data['fdywrlcb']  # 发电业务燃料成本
        worksheet.range(f'{col}36').value = data['grywrlcb']  # 供热业务燃料成本
        worksheet.range(f'{col}37').value = data['crlwcb']  # 除燃料外其它成本
        worksheet.range(f'{col}38').value = data['sf']  # 水费
        worksheet.range(f'{col}39').value = data['wgdf']  # 网购电费
        worksheet.range(f'{col}40').value = data['clf']  # 材料费
        worksheet.range(f'{col}41').value = data['zgxc']  # 职工薪酬
        worksheet.range(f'{col}42').value = data['gz']  # 工资
        worksheet.range(f'{col}43').value = data['zjf']  # 折旧费
        worksheet.range(f'{col}44').value = data['xlf']  # 修理费
        worksheet.range(f'{col}45').value = data['wtyx']  # 委托运行费
        worksheet.range(f'{col}46').value = data['qtfy']  # 其它费用
        worksheet.range(f'{col}47').value = data['qtywcb']  # 其它业务成本
        worksheet.range(f'{col}48').value = data['yysj']  # 营业税金
        worksheet.range(f'{col}49').value = data['cwfy']  # 财务费用
        worksheet.range(f'{col}51').value = data['glfy']  # 管理费用
        worksheet.range(f'{col}52').value = data['qtsy']  # 其它收益
        worksheet.range(f'{col}53').value = data['yywsr']  # 营业外收入
        worksheet.range(f'{col}54').value = data['yywzc']  # 营业外支出
        worksheet.range(f'{col}55').value = data['yffy']  # 研发费用
        
        # 计算并写入利润总额
        lr = (data['zyywsr'] + data['qtywsr'] - data['yycb'] - data['yysj'] - 
              data['cwfy'] + data['qtsy'] + data['yywsr'] - data['yywzc'] - 
              data['glfy'] - data['yffy'])
        worksheet.range(f'{col}56').value = lr

    def set_cell_formats(self, worksheet, col):
        """设置单元格格式"""
        try:
            print(f"开始设置{col}列格式")
            # 设置颜色1 - 浅红色
            color_range1 = (f'{col}3,{col}9:{col}12,{col}15:{col}17,{col}19,'
                           f'{col}22:{col}23,{col}25:{col}26,{col}31,'
                           f'{col}38:{col}49,{col}51:{col}55')
            worksheet.range(color_range1).color = (242, 220, 219)

            # 设置颜色2 - 浅蓝色
            color_range2 = (f'{col}4:{col}8,{col}13,{col}18,{col}20,{col}24,'
                           f'{col}27:{col}30,{col}32:{col}37,{col}56')
            worksheet.range(color_range2).color = (197, 217, 241)

            # 设置整体区域格式
            area = worksheet.range(f'{col}3:{col}56')
            #设置单元格显示为2位小数
            area.number_format = '0.00'
            
            # 设置单元格四周有细线 - 明确定义边框样式
            area.api.Borders.LineStyle = 1  # 细线
            area.api.Borders.Weight = 2     # 细线宽度
            
            #设置11行、13行和14行单元格显示为百分位，保留2位小数
            worksheet.range(f'{col}11,{col}13:{col}14').number_format = '0.00%'
            area.font.size = 10
            area.font.name = '仿宋'
            #设置14行和21行单元格底色为浅绿色
            worksheet.range(f'{col}14,{col}21').color = (235, 241, 222)
            
            print(f"{col}列格式设置完成")
        except Exception as e:
            print(f"设置{col}列格式时出错: {str(e)}")

    @staticmethod
    def get_column_letter(column_number):
        """将列号转换为Excel列字母"""
        result = ""
        while column_number > 0:
            column_number -= 1
            remainder = column_number % 26
            result = chr(65 + remainder) + result
            column_number //= 26
        return result

    def process_files(self):
        if not self.source_file.get() or not self.target_file.get():
            messagebox.showerror("错误", "请选择源文件和目标文件")
            return
            
        try:
            self.status_label.config(text="正在处理数据...")
            self.progress['value'] = 0
            self.window.update()
            
            print("开始打开Excel应用程序")
            app = xw.App(visible=False, add_book=False)
            print("打开源文件")
            workbook1 = app.books.open(self.source_file.get())
            worksheet1 = workbook1.sheets[0]
            print("打开目标文件")
            workbook2 = app.books.open(self.target_file.get())
            worksheet2 = workbook2.sheets('生产经营指标')

            # 获取动态列
            print("获取动态列")
            last_col = worksheet2.used_range.last_cell.column + 1
            col_letter = self.get_column_letter(last_col)
            print(f"动态列为: {col_letter}")

            try:
                # 处理D列数据到动态列
                #print("开始处理D列数据")
                self.progress['value'] = 25
                self.window.update()
                data_d = self.get_data_from_source(worksheet1, 'D')
                
                #print("将D列数据写入动态列")
                self.progress['value'] = 50
                self.window.update()
                self.write_data_to_target(worksheet2, col_letter, data_d)
                
                # 处理E列数据到K列
               # print("开始处理E列数据")
                self.progress['value'] = 75
                self.window.update()
                data_e = self.get_data_from_source(worksheet1, 'E')
                
               # print("将E列数据写入K列")
                self.progress['value'] = 90
                self.window.update()
                try:
                   # print("开始写入K列数据")
                    self.write_data_to_target(worksheet2, 'K', data_e)
                    #print("K列数据写入完成")
                except Exception as e:
                    print(f"写入K列时出错: {str(e)}")
                    # 尝试写入J列
                    #print("尝试写入J列数据")
                    self.write_data_to_target(worksheet2, 'J', data_e)
                    #print("J列数据写入完成")

                # 设置格式
                #print("设置单元格格式")
                try:
                    self.set_cell_formats(worksheet2, col_letter)
                    #print(f"{col_letter}列格式设置完成")
                except Exception as e:
                    print(f"设置{col_letter}列格式时出错: {str(e)}")
                
                try:
                    self.set_cell_formats(worksheet2, 'K')
                    #print("K列格式设置完成")
                except Exception as e:
                    print(f"设置K列格式时出错: {str(e)}")
                    try:
                        # 尝试设置J列格式
                        self.set_cell_formats(worksheet2, 'J')
                        #print("J列格式设置完成")
                    except Exception as e:
                        print(f"设置J列格式时出错: {str(e)}")

                self.progress['value'] = 100
                self.window.update()
                
                messagebox.showinfo("成功", "数据处理完成！")
                self.status_label.config(text="处理完成")
                
            finally:
                #print("关闭工作簿")
                workbook1.close()
                workbook2.save()
                workbook2.close()
                app.quit()
                
        except Exception as e:
            print(f"发生错误: {str(e)}")
            messagebox.showerror("错误", f"处理过程中出现错误：{str(e)}")
            self.status_label.config(text="处理出错")

    def run(self):
        self.window.mainloop()

if __name__ == "__main__":
    app = ExcelProcessor()
    app.run()